﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;

public partial class Sys_dataimport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Session["tid"] != null && Session["usertype"] != null)
            {
                if (Session["usertype"].ToString() == "4")
                {
                    //
                }
            }
            else
            {
                PM.Code.Web.alertmessage.ShowMsg("账号登录已超时，请返回重新登录。", "Index.aspx");
                return;
            }
        }
    }

    protected void CheckCollum_Click(object sender, EventArgs e)
    {

    }

    protected void btnFileOut_Click(object sender, EventArgs e)
    {
        //命名文件并上传文件
        string fileid = PM.PMBasic.Basic.GetIndentityWithDate("附件");
        string result = UploadFile(fileid);
        if (result == "1")
        {
            PM.Code.Web.alertmessage.ShowMsg("不是MS Excel格式（.xls,.xlsx）!");
            return;
        }
        else if (result == "2")
        {
            PM.Code.Web.alertmessage.ShowMsg("您的文件的大小超过4M，建议您分为多个文件！");
            return;
        }
        else if (result == "3")
        {
            PM.Code.Web.alertmessage.ShowMsg("您未选择任何文件!");
            return;
        }
        else
        {
            //将Excel中的数据上传到
            DataSet ds = GetExcelData(result);
            //将数据插入到Sql Server中
            PM.Code.Web.input.DataSetToSql(ds, this.DropDownList1.SelectedValue.Trim(), "1,2,3,4,5,");
        }
    }

    private string UploadFile(string id)
    {
        if (this.FileUpload1.HasFile)
        {
            string fileName = System.IO.Path.GetFileName(this.FileUpload1.PostedFile.FileName);
            string fileExtension = System.IO.Path.GetExtension(fileName);

            //如果格式都不符合则返回
            if (fileExtension != ".xls" && fileExtension != ".xlsx")
                return "1";
            //如果大小超过4M则返回
            if (FileUpload1.PostedFile.ContentLength / 1024 / 1024 >= 4)
                return "2";
            string phyPath = Server.MapPath("../ckfinder/userfiles/files/数据库导入文件");
            //判断路径是否存在,若不存在则创建路径
            DirectoryInfo upDir = new DirectoryInfo(phyPath);
            if (!upDir.Exists)
                upDir.Create();
            string filename = phyPath + "//" + id + "." + fileExtension;
            //保存文件
            try
            {
                this.FileUpload1.SaveAs(filename);
            }
            catch
            {
                throw new ApplicationException("上传失败!");
            }
            return filename;//成功
        }
        else
            return "3";//未选择文件
    }
    /// <summary>
    /// 创建表头
    /// </summary>
    /// <param name="ds">DataSet</param>
    private void CreateFields(DataSet ds)
    {
        //检查除了第一行标头
        if (ds.Tables[0].Rows.Count > 1)
        {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                str.Append(ds.Tables[0].Columns[i].ToString()).Append(",");
            }
            this.DivOldTitle.InnerText = str.ToString();
        }
        else
            this.DivOldTitle.InnerText = "无任何数据，请检查您的Excel文档有无数据";
    }
    ///<summary>
    ///从Excel中获取数据
    ///</summary>
    ///<returns>dataset ds</returns>
    private DataSet GetExcelData(string filepath)
    {
        DataSet ds = new DataSet();
        /*******************************************************错误方法********************************************************************/
        //说明：由于IE8+，FireFox3+浏览器都处于安全考虑，无法提供默认的上传完整路径功能的开启，
        //this.FileUpload1.PostedFile.FileName只能获取文件名，因此检测浏览器有没有开启“完整上传路径”的选项，
        //这很麻烦，因此我们采取JS获取的方法或者干脆直接传到服务器
        /*******************************************************错误方法********************************************************************/
        //以上方法错误，因为不可能获得客户端的路径，再通过连接字符串来在线获取数据，这样只能获取服务器上的有关文件
        //只能先前台JS检验其合法性，在上传到服务器，然后再导入到Sql Server中
        string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
        string queryStr = "SELECT * FROM [Sheet1$]";
        OleDbConnection conn03 = new OleDbConnection(connStr03);
        OleDbConnection conn07 = new OleDbConnection(connStr07);
        if (this.FileUpload1.HasFile)
        {
            string fileExt = System.IO.Path.GetExtension(this.FileUpload1.FileName);
            if (fileExt == ".xls")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
                myAdapter.Fill(ds);
            }
            else if (fileExt == ".xlsx")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn07);
                myAdapter.Fill(ds);
            }
            else
            {
                this.DivOldTitle.InnerText = "文件不存在";
            }
        }
        return ds;
    }

    ///<summary>
    ///该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表,此用Sheet1；
    ///</summary>
    ///<param name="ds">ds</param>
    private void InsertDB(DataSet ds)
    {
        SqlConnection _con = new SqlConnection(@"Data Source=STKWX028\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = _con;
        StringBuilder sb = new StringBuilder();
        if (ds.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                sb.Append("INSERT INTO bookInfo(bookID,bookName,author,publisher,price,readerID,readerType,bookStatus) VALUES('");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[0].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[1].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[2].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[3].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[4].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[5].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[6].ToString() + "','");
                sb.Append(ds.Tables[0].Rows[i].ItemArray[7].ToString() + "' )");
                cmd.CommandText = sb.ToString();
            }
        }
        _con.Open();
        int j = cmd.ExecuteNonQuery();
        _con.Close();
        if (j > 0)
        {
            this.DivOldTitle.InnerText = "Insert into DB table Sucessfully!";
        }
    }
}

